Análise de projetos de infraestrutura com investimento federal no Distrito Federal¶
Teste avaliativo para vaga de bolsista em engenharia/análise de dados (LabLivre). Ver material de referência. Fonte de dados: obrasgov.
Devido às visualizações interativas e limitações do github, é altamente recomendada a visualização deste notebook por meio da página html.
Relatório detalhado de tratamento e análise de dados. Para resumo acessível com visualizações, ver seção 4. Resultados e conclusão.
Observação: IA (Cursor) foi usado para a produção deste relatório, principalmente para agilizar a produção de gráficos.
Sumário:
- Extração de dados
- Tratamento de dados
- Duplicatas
- Valores ausentes
- Limpeza específica de dados
- Tipagem de dados
- Colunas aninhadas
- Engenharia de características
- Carregamento final dos dados
- Análise de dados
- Informações básicas
- Qualidade dos dados
- Análise categórica
- Análise de investimento
- Análise temporal
- Dados geográficos (GIS)
- Processamento de linguagem natural (NLP)
- Resultados e conclusão
import json
import time
import requests
import glob
import sqlite3
import datetime
# dados e visualização
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import altair as alt
# estatística
import pingouin as pg
import statsmodels.api as sm
# machine learning e NLP
import hdbscan
import umap.umap_ as umap
# mapa
import folium
# cores do lablivre
palette = ["#412355", "#F2701C", "#18CEE6"]
1. Extração de dados¶
# o script de coleta de dados foi executado separadamente
def get_data(page: int) -> dict:
url = "https://api.obrasgov.gestao.gov.br/obrasgov/api/projeto-investimento"
params = {"uf": "DF", "pagina": page, "tamanhoDaPagina": 100}
headers = {"accept": "*/*"}
response = requests.get(url, params=params, headers=headers)
if response.status_code != 200:
raise Exception(f"Failed to get data: {response.status_code}")
return response.json()
def main():
# deselegante mas resolve imediatamente
for page in range(100):
response = get_data(page)
print(f"Page {page} processed")
# salvar dados brutos para não depender da api
with open(f"data/data-{page}.json", "w", encoding="utf-8") as f:
json.dump(response["content"], f, indent=4)
print(f"Data saved to data/data-{page}.json")
time.sleep(1)
page += 1
# if __name__ == "__main__":
# main()
def load_json_files() -> list[dict]:
json_files = glob.glob("data/data-*.json")
all_records = []
for file in json_files:
with open(file, "r", encoding="utf-8") as f:
records = json.load(f)
all_records.extend(records)
return all_records
Dados iniciais:
df = pd.DataFrame(load_json_files())
df.info(memory_usage="deep")
<class 'pandas.core.frame.DataFrame'> RangeIndex: 834 entries, 0 to 833 Data columns (total 31 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 idUnico 834 non-null object 1 nome 834 non-null object 2 cep 400 non-null object 3 endereco 430 non-null object 4 descricao 834 non-null object 5 funcaoSocial 834 non-null object 6 metaGlobal 834 non-null object 7 dataInicialPrevista 832 non-null object 8 dataFinalPrevista 832 non-null object 9 dataInicialEfetiva 23 non-null object 10 dataFinalEfetiva 7 non-null object 11 dataCadastro 834 non-null object 12 especie 830 non-null object 13 natureza 834 non-null object 14 naturezaOutras 211 non-null object 15 situacao 834 non-null object 16 descPlanoNacionalPoliticaVinculado 287 non-null object 17 uf 834 non-null object 18 qdtEmpregosGerados 164 non-null object 19 descPopulacaoBeneficiada 174 non-null object 20 populacaoBeneficiada 169 non-null object 21 observacoesPertinentes 129 non-null object 22 isModeladaPorBim 591 non-null object 23 dataSituacao 834 non-null object 24 tomadores 834 non-null object 25 executores 834 non-null object 26 repassadores 834 non-null object 27 eixos 834 non-null object 28 tipos 834 non-null object 29 subTipos 834 non-null object 30 fontesDeRecurso 834 non-null object dtypes: object(31) memory usage: 2.3 MB
pd.set_option("display.max_columns", None)
df.head()
| idUnico | nome | cep | endereco | descricao | funcaoSocial | metaGlobal | dataInicialPrevista | dataFinalPrevista | dataInicialEfetiva | dataFinalEfetiva | dataCadastro | especie | natureza | naturezaOutras | situacao | descPlanoNacionalPoliticaVinculado | uf | qdtEmpregosGerados | descPopulacaoBeneficiada | populacaoBeneficiada | observacoesPertinentes | isModeladaPorBim | dataSituacao | tomadores | executores | repassadores | eixos | tipos | subTipos | fontesDeRecurso | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1828.53-16 | SISEG - Sistema integrado de segurança | 1 | implantação dos sistemas de monitoramento por ... | implantação dos sistemas de monitoramento por ... | Modernização do sistema de segurança do Banco ... | 2019-02-04 | 2024-02-06 | None | None | 2021-05-12 | Recuperação | Projeto | Cadastrada | None | DF | None | None | None | None | None | 2021-05-12 | [] | [{'nome': 'BANCO CENTRAL DO BRASIL', 'codigo':... | [] | [{'id': 1, 'descricao': 'Administrativo'}] | [{'id': 5, 'descricao': 'Administrativo', 'idE... | [{'id': 59, 'descricao': 'Obras em Imóveis de ... | [{'origem': 'Federal', 'valorInvestimentoPrevi... | ||
| 1 | 1711.53-18 | Reforma do espelho d'água do Edifício-Sede do ... | 1 | Reforma do espelho d'água do Edifício-Sede do ... | Reestabelecer a impermeabilização do espelho d... | Recuperação do sistema de impermeabilização e ... | 2021-01-04 | 2021-05-18 | None | None | 2021-05-04 | Reforma | Obra | Cadastrada | None | DF | None | None | None | None | None | 2021-05-04 | [{'nome': 'BANCO CENTRAL DO BRASIL', 'codigo':... | [{'nome': 'BANCO CENTRAL DO BRASIL', 'codigo':... | [] | [{'id': 1, 'descricao': 'Administrativo'}] | [{'id': 5, 'descricao': 'Administrativo', 'idE... | [{'id': 59, 'descricao': 'Obras em Imóveis de ... | [{'origem': 'Federal', 'valorInvestimentoPrevi... | ||
| 2 | 1989.53-01 | Elaboração de projetos arquitetônicos e de eng... | 1 | Campus Universitário Darcy Ribeiro, Brasília - DF | Contratação de empresa especializada para elab... | Obras para unidades acadêmicas de ensino e pes... | Elaboração de projetos de engenharia e Arquite... | 2021-05-18 | 2021-09-29 | None | None | 2021-05-17 | Construção | Projeto | None | Cadastrada | None | DF | None | None | None | None | None | 2021-05-17 | [] | [{'nome': 'FUNDACAO UNIVERSIDADE DE BRASILIA',... | [{'nome': 'FUNDACAO UNIVERSIDADE DE BRASILIA',... | [{'id': 1, 'descricao': 'Administrativo'}] | [{'id': 8, 'descricao': 'Educação', 'idEixo': 1}] | [{'id': 46, 'descricao': 'Instituições Federai... | [{'origem': 'Federal', 'valorInvestimentoPrevi... |
| 3 | 2004.53-58 | SISEG - Sistema integrado de segurança | 1 | implantação dos sistemas de monitoramento por ... | implantação dos sistemas de monitoramento por ... | Modernização do sistema de segurança do Banco ... | 2019-02-04 | 2024-02-06 | None | None | 2021-05-18 | Recuperação | Projeto | Cadastrada | None | DF | None | None | None | None | None | 2021-05-18 | [{'nome': 'BANCO CENTRAL DO BRASIL-ORC.FISCAL/... | [{'nome': 'BANCO CENTRAL DO BRASIL', 'codigo':... | [] | [{'id': 1, 'descricao': 'Administrativo'}] | [{'id': 5, 'descricao': 'Administrativo', 'idE... | [{'id': 59, 'descricao': 'Obras em Imóveis de ... | [{'origem': 'Federal', 'valorInvestimentoPrevi... | ||
| 4 | 2617.53-02 | CENTRO DE FORMAÇÃO TECNOLOGICA - CFT - CAMPUS ... | 1 | Área Especial n° 01, Quadra 16, Cidade do Auto... | construção um galpão com pé direito alto, que ... | atender a comunidade acadêmica do Campus Estru... | aumentar a oferta de cursos técnicos/tecnológi... | 2020-12-01 | 2021-10-27 | None | None | 2021-06-18 | Construção | Obra | Cadastrada | triplicar as matrículas da educação profission... | DF | None | None | None | None | None | 2021-06-18 | [{'nome': 'INSTITUTO FED. ED. CIENCIA E TEC. D... | [{'nome': 'INSTITUTO FED. ED. CIENCIA E TEC. D... | [{'nome': 'MINISTÉRIO DA EDUCAÇÃO', 'codigo': ... | [{'id': 4, 'descricao': 'Social'}] | [{'id': 46, 'descricao': 'Educação', 'idEixo':... | [{'id': 84, 'descricao': 'Educação', 'idTipo':... | [{'origem': 'Federal', 'valorInvestimentoPrevi... |
2. Tratamento de dados¶
# colunas relativa a dados aninhados, represetando dados com relação many to many
nested_cols = [
"tomadores",
"executores",
"repassadores",
"eixos",
"tipos",
"subTipos",
"fontesDeRecurso",
]
2.1 Duplicatas¶
from analyse_duplicates import check_duplicates, analyze_false_duplicates
# existem duplicatas que só diferem no conteúdo das listas.
# não quero te entediar com esse código gerado por IA então movi para outro arquivo
# Para mais detalhes, consultar o arquivo `apendices/duplicate_records_report.txt`.
check_duplicates(df)
analyze_false_duplicates(df, "apendices/duplicate_records_report")
Columns with lists: ['tomadores', 'executores', 'repassadores', 'eixos', 'tipos', 'subTipos', 'fontesDeRecurso'] Number of duplicate rows (not considering list columns): 122 Number of duplicate rows: 87 Number of 'false duplicate' rows: 70 Unique idUnico values in false duplicates: 35 Report written to duplicate_records_report.txt
Após investigação detalhada, todas as 'falsas duplicatas' tem o seguinte formato:
133:
{
id: 90,
descricao: "Preservação do Patrimônio",
idTipo: 5
}
491:
{
id: 90,
descricao: "Preservação do Patrimônio",
idTipo: 43
}
Isso mostra que são duplicatas -- portanto serão removidas.
df.duplicated(subset=["idUnico"]).sum()
df.drop_duplicates(subset=["idUnico"], inplace=True)
2.2 Valores ausentes¶
# checar apenas colunas que não são aninhadas
object_cols = df.drop(columns=nested_cols, axis=1)
info_df = pd.DataFrame(
{
"NaN Values": object_cols.isna().sum(),
"% NaN": (object_cols.isna().sum() / len(object_cols) * 100).round(1),
}
)
info_df[info_df["% NaN"] > 1].sort_values(by="% NaN", ascending=False)
| NaN Values | % NaN | |
|---|---|---|
| dataFinalEfetiva | 707 | 99.3 |
| dataInicialEfetiva | 690 | 96.9 |
| observacoesPertinentes | 606 | 85.1 |
| qdtEmpregosGerados | 574 | 80.6 |
| populacaoBeneficiada | 572 | 80.3 |
| descPopulacaoBeneficiada | 568 | 79.8 |
| naturezaOutras | 522 | 73.3 |
| descPlanoNacionalPoliticaVinculado | 464 | 65.2 |
| cep | 365 | 51.3 |
| endereco | 337 | 47.3 |
| isModeladaPorBim | 216 | 30.3 |
hoje = datetime.datetime.now()
df["dataFinalPrevista"] = pd.to_datetime(df["dataFinalPrevista"])
# Calcular quantos projetos estão atrasados
atrasados = df[df["dataFinalPrevista"] < hoje]
print(
f"{len(atrasados)} projetos ({(len(atrasados)/len(df)*100):.1f}%) estão com data final prevista no passado"
)
515 projetos (72.3%) estão com data final prevista no passado
Há muitos valores ausentes que provavelmente não são erros (como em campos do tipo 'outras observações'). Mais notável é a ausência de qdtEmpregosGerados, populacaoBeneficiada, cep e endereco.
Considerando que 75% dos projetos possuem data final prevista no passado, a ausência de datas indica que estes dados não foram atualizados.
2.3 Limpeza específica de dados¶
Encoding¶
# erro de encoding. Texto em UTF-8, foi lido como Latin-1 (ISO-8859-1)
# toddos tem uma rodovia como endereço, indicando que o erro é relacionado à fonte dos dados.
enconding_issue = df[df["descricao"].str.contains("çÃ")]
print(f"Pelo menos {enconding_issue.shape[0]} projetos com erro de encoding")
print("exemplo:", df[df["descricao"].str.contains("çÃ")].head(1)["descricao"])
Pelo menos 18 projetos com erro de encoding exemplo: 63 prestação dos serviços de gerenciamento dos... Name: descricao, dtype: object
def fix_encoding(item: any) -> any:
if isinstance(item, str):
try:
return item.encode("latin-1").decode("utf-8")
except:
return item
return item
for col in df.select_dtypes(include=["object"]).columns:
df[col] = df[col].apply(fix_encoding)
Registros incorretos¶
# há um sujeito chamado Ronald Alves Vieira e ele está testando em produção
test_df = df[df["nome"].str.contains("Ronald", case=False, na=False)]
test_df.head()
| idUnico | nome | cep | endereco | descricao | funcaoSocial | metaGlobal | dataInicialPrevista | dataFinalPrevista | dataInicialEfetiva | dataFinalEfetiva | dataCadastro | especie | natureza | naturezaOutras | situacao | descPlanoNacionalPoliticaVinculado | uf | qdtEmpregosGerados | descPopulacaoBeneficiada | populacaoBeneficiada | observacoesPertinentes | isModeladaPorBim | dataSituacao | tomadores | executores | repassadores | eixos | tipos | subTipos | fontesDeRecurso | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 79 | 4543.53-79 | 202111-22-Ronald 1 | 70067-901 | 2021122-Ronald - Endereço Completo | 2021122-Ronald - Descrição do Projeto | 2021122-Ronald - Descrição Funç]ap Social | 20211122-Ronald - Descrição Meta Global | 2021-12-10 | 2021-12-10 | None | None | 2021-11-17 | Fabricação | Projeto | outros | Cadastrada | None | DF | None | None | None | None | None | 2021-11-17 | [{'nome': 'FUND.UNIVERSIDADE FEDERAL VALE SAO ... | [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... | [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... | [{'id': 4, 'descricao': 'Social'}] | [{'id': 45, 'descricao': 'Desenvolvimento', 'i... | [{'id': 38, 'descricao': 'Saneamento', 'idTipo... | [{'origem': 'Federal', 'valorInvestimentoPrevi... |
| 80 | 4540.53-87 | 202111-22-Ronald 1 | 70067-901 | 2021122-Ronald - Endereço Completo | 2021122-Ronald - Descrição do Projeto | 2021122-Ronald - Descrição Funç]ap Social | 20211122-Ronald - Descrição Meta Global | 2021-12-10 | 2021-12-10 | None | None | 2021-11-17 | Reforma | Obra | outros | Cadastrada | None | DF | None | None | None | None | None | 2021-11-17 | [{'nome': 'FUND.UNIVERSIDADE FEDERAL VALE SAO ... | [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... | [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... | [{'id': 4, 'descricao': 'Social'}] | [{'id': 45, 'descricao': 'Desenvolvimento', 'i... | [{'id': 38, 'descricao': 'Saneamento', 'idTipo... | [{'origem': 'Federal', 'valorInvestimentoPrevi... |
| 82 | 4542.53-01 | 202111-22-Ronald 1 | 70067-901 | 2021122-Ronald - Endereço Completo | 2021122-Ronald - Descrição do Projeto | 2021122-Ronald - Descrição Funç]ap Social | 20211122-Ronald - Descrição Meta Global | 2021-12-10 | 2021-12-10 | None | None | 2021-11-17 | Reforma | Projeto de Investimento em Infraestrutura | outros | Cadastrada | None | DF | None | None | None | None | None | 2021-11-17 | [{'nome': 'FUND.UNIVERSIDADE FEDERAL VALE SAO ... | [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... | [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... | [{'id': 4, 'descricao': 'Social'}] | [{'id': 45, 'descricao': 'Desenvolvimento', 'i... | [{'id': 38, 'descricao': 'Saneamento', 'idTipo... | [{'origem': 'Federal', 'valorInvestimentoPrevi... |
| 83 | 4544.53-36 | 202111-22-Ronald 1 | 70067-901 | 2021122-Ronald - Endereço Completo | 2021122-Ronald - Descrição do Projeto | 2021122-Ronald - Descrição Funç]ap Social | 20211122-Ronald - Descrição Meta Global | 2021-12-10 | 2021-12-10 | None | None | 2021-11-17 | Reforma | Projeto de Investimento em Infraestrutura | outros | Cadastrada | None | DF | None | None | None | None | None | 2021-11-17 | [{'nome': 'FUND.UNIVERSIDADE FEDERAL VALE SAO ... | [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... | [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... | [{'id': 4, 'descricao': 'Social'}] | [{'id': 45, 'descricao': 'Desenvolvimento', 'i... | [{'id': 38, 'descricao': 'Saneamento', 'idTipo... | [{'origem': 'Federal', 'valorInvestimentoPrevi... |
| 205 | 4541.53-44 | 202111-22-Ronald 1 | 70067-901 | 2021122-Ronald - Endereço Completo | 2021122-Ronald - Descrição do Projeto | 2021122-Ronald - Descrição Funç]ap Social | 20211122-Ronald - Descrição Meta Global | 2021-12-10 | 2021-12-10 | None | None | 2021-11-17 | Recuperação | Projeto de Investimento em Infraestrutura | outros | Cadastrada | None | DF | None | None | None | None | None | 2021-11-17 | [{'nome': 'FUND.UNIVERSIDADE FEDERAL VALE SAO ... | [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... | [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... | [{'id': 4, 'descricao': 'Social'}] | [{'id': 45, 'descricao': 'Desenvolvimento', 'i... | [{'id': 38, 'descricao': 'Saneamento', 'idTipo... | [{'origem': 'Federal', 'valorInvestimentoPrevi... |
# é possível que hajam mais casos que não encontrei
# é possível que essa limpeza remova dados reais
antes = df.shape[0]
df = df[~df["nome"].str.contains("Ronald", case=False, na=False)]
df = df[~df["nome"].str.contains("Teste", case=False, na=False)]
depois = df.shape[0]
print(
f"Removidos {antes - depois} projetos por serem registros falsos (efeitos colaterais de testes de integração)"
)
Removidos 22 projetos por serem registros falsos (efeitos colaterais de testes de integração)
Endereços e CEP¶
# remover ceps preenchidos com 1 ou espaço.
df["cep"] = df["cep"].str.replace(r"^1$", "", regex=True)
df["cep"] = df["cep"].str.replace(r"\s+", "", regex=True).replace("", None)
# normalizar ceps
df["cep"] = df["cep"].str.replace("-", "")
df["cep"] = df["cep"].str.replace(".", "")
# remover ceps com menos de 8 dígitos
df["cep"] = df["cep"].apply(lambda x: None if pd.isna(x) or len(str(x)) < 8 else x)
df["cep"].nunique()
88
# remover enderecos preenchidos com 1 ou espaço.
df["endereco"] = df["endereco"].str.strip()
df["endereco"] = df["endereco"].str.replace(r"^1$", "", regex=True)
df["endereco"] = df["endereco"].str.replace(r"^\s+$", "", regex=True).replace("", None)
df["endereco"].nunique()
233
2.4 Tipagem¶
df["isModeladaPorBim"] = df["isModeladaPorBim"].astype("boolean")
# variaveis categoricas
df["natureza"] = df["natureza"].astype("category")
df["situacao"] = df["situacao"].astype("category")
df["especie"] = df["especie"].astype("category")
df["uf"] = df["uf"].astype("category")
# variaveis de data
# verifiquei manualmente os registros para erros de formatação mas não encontrei problemas
date_cols = [
"dataInicialPrevista",
"dataFinalPrevista",
"dataInicialEfetiva",
"dataFinalEfetiva",
"dataCadastro",
"dataSituacao",
]
for col in date_cols:
df[col] = pd.to_datetime(df[col], errors="coerce")
# alguns registros tem strings em vez de ints. esses registros também têm outros problemas; veja abaixo.
# converte para float em vez de int para permitir valores nulos, e transforma strings em nulos
df["qdtEmpregosGerados"] = pd.to_numeric(df["qdtEmpregosGerados"], errors="coerce")
df["populacaoBeneficiada"] = pd.to_numeric(df["populacaoBeneficiada"], errors="coerce")
2.5 Colunas aninhadas¶
Dados via json representam dados aninhados que precisam ser normalizados para armazenamento em um banco de dados relacional.
def normalize_nested_column(
df: pd.DataFrame, id_col: str, nested_col: str
) -> pd.DataFrame:
def convert_dict_values_to_str(x):
# pd.json_normalize(df['tomadores'].explode()) converte o id para float, o que pode causar problemas devido à imprecisão de ponto flutuante mesmo se convertido de volta para int/str. por isso, preciso converter os valores para strings primeiro, o que requer um código um pouco confuso:
if not isinstance(x, list):
return x
return [{key: str(value) for key, value in item.items()} for item in x]
df[nested_col] = df[nested_col].apply(convert_dict_values_to_str)
exploded = df[[id_col, nested_col]].explode(nested_col)
exploded = exploded.dropna(subset=[nested_col])
normalized = pd.json_normalize(exploded[nested_col])
normalized[id_col] = exploded[id_col].values
return normalized
def create_entity_table_from_junction_table(
old_df: pd.DataFrame, prev_index: str, actual_index: str
) -> pd.DataFrame:
new_df = old_df.dropna(subset=[prev_index])
new_df.drop_duplicates(subset=[actual_index], inplace=True)
new_df.set_index(actual_index, inplace=True)
new_df.drop(prev_index, axis=1, inplace=True)
return new_df
# reuni essas tabelas em uma só, pois são todas referentes a instituicoes e usam o mesmo sistema de códigos.
projeto_tomadores_df = normalize_nested_column(df, "idUnico", "tomadores")
projeto_executores_df = normalize_nested_column(df, "idUnico", "executores")
projeto_repassadores_df = normalize_nested_column(df, "idUnico", "repassadores")
tomadores_df = create_entity_table_from_junction_table(
projeto_tomadores_df, "idUnico", "codigo"
)
executores_df = create_entity_table_from_junction_table(
projeto_executores_df, "idUnico", "codigo"
)
repassadores_df = create_entity_table_from_junction_table(
projeto_repassadores_df, "idUnico", "codigo"
)
instituicoes_df = pd.concat([tomadores_df, executores_df, repassadores_df])
# deduplicate indices
instituicoes_df = instituicoes_df.loc[~instituicoes_df.index.duplicated(keep="first")]
instituicoes_df.head(1)
| nome | |
|---|---|
| codigo | |
| 25280 | BANCO CENTRAL DO BRASIL |
projeto_eixos_df = normalize_nested_column(df, "idUnico", "eixos")
eixos_df = create_entity_table_from_junction_table(projeto_eixos_df, "idUnico", "id")
eixos_df.head(1)
| descricao | |
|---|---|
| id | |
| 1 | Administrativo |
projeto_tipos_df = normalize_nested_column(df, "idUnico", "tipos")
tipos_df = create_entity_table_from_junction_table(projeto_tipos_df, "idUnico", "id")
tipos_df.head(1)
| descricao | idEixo | |
|---|---|---|
| id | ||
| 5 | Administrativo | 1 |
projeto_subtipos_df = normalize_nested_column(df, "idUnico", "subTipos")
subtipos_df = create_entity_table_from_junction_table(
projeto_subtipos_df, "idUnico", "id"
)
subtipos_df.head(1)
| descricao | idTipo | |
|---|---|---|
| id | ||
| 59 | Obras em Imóveis de Uso Público | 5 |
# nesse caso é one to many e nao many to many, entao nao precisa da tabela intermediaria
fontes_de_recurso_df = normalize_nested_column(
df, "idUnico", "fontesDeRecurso"
).reset_index(drop=True)
# nesse caso estava certo o float
fontes_de_recurso_df["valorInvestimentoPrevisto"] = pd.to_numeric(
fontes_de_recurso_df["valorInvestimentoPrevisto"], errors="coerce"
)
fontes_de_recurso_df.head(1)
| origem | valorInvestimentoPrevisto | idUnico | |
|---|---|---|---|
| 0 | Federal | 23427554.88 | 1828.53-16 |
df.drop(columns=nested_cols, inplace=True)
Foram criadas 5 novas tabelas com entidades:
instituicoes_dfeixos_dftipos_dfsubtipos_dffontes_de_recurso_df
Além de 6 novas tabelas de junção:
projeto_tomadores_dfprojeto_executores_dfprojeto_repassadores_dfprojeto_eixos_dfprojeto_tipos_dfprojeto_subtipos_df
2.5 Engenharia de características¶
Criação de novas colunas para fins de análise
textoTotal: concatenação de dados textuais para processamento de linguagem natural (NLP)lat_viacep,lon_viacep,lat_ipedf,lon_ipedf: dados geográficos (obtidos por geocodificação viacep) usando dois métodos diferentes.investimentoTotal: consolidação defontesDeRecursopara análise quantitativainvestimentoFaixa: versão categórica deinvestimentoTotalpor faixas.isInvestimentoSimbolico: investimentos com valores menores que 1 real.duracaoPrevista: duração prevista de projetos (viadataInicialPrevista,dataFinalPrevista)
textoTotal¶
text_cols = [
"nome",
"endereco",
"descricao",
"funcaoSocial",
"metaGlobal",
"especie",
"natureza",
"naturezaOutras",
"situacao",
"descPlanoNacionalPoliticaVinculado",
"descPopulacaoBeneficiada",
"observacoesPertinentes",
]
def make_text_total(row):
text_total = []
for x in row:
if pd.notna(x):
text_total.append(str(x))
return "\n\n".join(text_total)
df["textoTotal"] = df[text_cols].apply(make_text_total, axis=1)
investimentoTotal¶
valor_por_id = (
fontes_de_recurso_df.groupby("idUnico")["valorInvestimentoPrevisto"]
.sum()
.reset_index()
)
valor_por_id.rename(
columns={"valorInvestimentoPrevisto": "investimentoTotal"}, inplace=True
)
df = df.merge(valor_por_id, on="idUnico", how="left", validate="one_to_one")
investimentoFaixa¶
bins = [
0,
1,
10,
100,
1_000,
10_000,
100_000,
1_000_000,
10_000_000,
100_000_000,
1_000_000_000,
np.inf,
]
labels = [
"<1",
"1-10",
"10-100",
"100-1k",
"1k-10k",
"10k-100k",
"100k-1M",
"1M-10M",
"10M-100M",
"100M-1B",
">1B",
]
df["investimentoFaixa"] = pd.cut(df["investimentoTotal"], bins=bins, labels=labels)
def plot_investment_ranges(df, palette):
faixa_counts = df["investimentoFaixa"].value_counts().reset_index()
faixa_counts.columns = ["investimentoFaixa", "count"]
plt.figure(figsize=(10, 6))
ax = sns.barplot(
data=faixa_counts,
x="investimentoFaixa",
y="count",
hue="investimentoFaixa",
palette=[palette[0] for _ in range(len(faixa_counts))],
)
# custom palette creates multiple containers
for container in ax.containers:
ax.bar_label(container) # type: ignore
plt.xlabel("Faixa de Investimento (R$)")
plt.ylabel("Projetos")
plt.title("Projetos por Faixa de Investimento")
plt.tight_layout()
plt.show()
plot_investment_ranges(df, palette)
isInvestimentoSimbolico¶
df["isInvestimentoSimbolico"] = df["investimentoTotal"] <= 1
df["isInvestimentoSimbolico"].value_counts()
isInvestimentoSimbolico False 571 True 119 Name: count, dtype: int64
duracaoPrevista¶
df["duracaoPrevista"] = df["dataFinalPrevista"] - df["dataInicialPrevista"]
df["duracaoPrevista"] = df["duracaoPrevista"].dt.days
df["duracaoPrevista"] = df["duracaoPrevista"].astype(float)
df["duracaoPrevista"].describe()
count 688.000000 mean 750.819767 std 688.251682 min 0.000000 25% 291.750000 50% 469.000000 75% 1090.250000 max 4827.000000 Name: duracaoPrevista, dtype: float64
Latitude e longitude¶
total = len(df)
missing = df["cep"].isna().sum()
print(f"Missing CEP values: {missing} out of {total} ({missing/total:.1%})")
ceps = df["cep"].dropna().unique()
Missing CEP values: 475 out of 690 (68.8%)
from geocode_ceps import cep_to_coords_viacep, cep_to_coords_ipedf
# para detalhes de obtenção dos valores, o arquivo `geocode_ceps.py`
# cep_to_coords_viacep(ceps.tolist(), "data/cep_coords_viacep.json")
# cep_to_coords_ipedf(ceps.tolist(), "data/cep_coords_ipedf.json")
with open("data/cep_coords_viacep.json") as f:
data = json.load(f)
df_viacep = pd.DataFrame.from_dict(data, orient="index")
df_viacep.columns = ["latitude", "longitude"]
print(
f"Null records: {df_viacep['latitude'].isna().sum()} out of {len(df_viacep)} ({df_viacep['latitude'].isna().sum()/len(df_viacep):.1%})"
)
with open("data/cep_coords_ipedf.json") as f:
data = json.load(f)
df_ipedf = pd.DataFrame.from_dict(data, orient="index")
df_ipedf.columns = ["latitude", "longitude"]
print(
f"Null records: {df_ipedf['latitude'].isna().sum()} out of {len(df_ipedf)} ({df_ipedf['latitude'].isna().sum()/len(df_ipedf):.1%})"
)
df = df.merge(
df_viacep, left_on="cep", right_index=True, how="left", suffixes=("", "_viacep")
)
df = df.merge(
df_ipedf, left_on="cep", right_index=True, how="left", suffixes=("", "_ipedf")
)
df = df.rename(
columns={
"latitude": "lat_viacep",
"longitude": "lon_viacep",
"latitude_ipedf": "lat_ipedf",
"longitude_ipedf": "lon_ipedf",
}
)
print(
f"Records with coordinates from both sources: {df[['lon_viacep', 'lon_ipedf']].notna().all(axis=1).sum()}"
)
# Count records with and without coordinates
has_viacep = df[["lat_viacep", "lon_viacep"]].notna().all(axis=1)
has_ipedf = df[["lat_ipedf", "lon_ipedf"]].notna().all(axis=1)
print(
f"Records with coordinates from either source: {(has_viacep | has_ipedf).sum()} ({(has_viacep | has_ipedf).sum()/len(df):.1%})"
)
Null records: 55 out of 88 (62.5%) Null records: 47 out of 88 (53.4%) Records with coordinates from both sources: 29 Records with coordinates from either source: 129 (18.7%)
Carregamento de dados¶
df.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 690 entries, 0 to 689 Data columns (total 33 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 idUnico 690 non-null object 1 nome 690 non-null object 2 cep 215 non-null object 3 endereco 293 non-null object 4 descricao 690 non-null object 5 funcaoSocial 690 non-null object 6 metaGlobal 690 non-null object 7 dataInicialPrevista 688 non-null datetime64[ns] 8 dataFinalPrevista 688 non-null datetime64[ns] 9 dataInicialEfetiva 21 non-null datetime64[ns] 10 dataFinalEfetiva 5 non-null datetime64[ns] 11 dataCadastro 690 non-null datetime64[ns] 12 especie 686 non-null category 13 natureza 690 non-null category 14 naturezaOutras 171 non-null object 15 situacao 690 non-null category 16 descPlanoNacionalPoliticaVinculado 248 non-null object 17 uf 690 non-null category 18 qdtEmpregosGerados 35 non-null float64 19 descPopulacaoBeneficiada 144 non-null object 20 populacaoBeneficiada 35 non-null float64 21 observacoesPertinentes 106 non-null object 22 isModeladaPorBim 493 non-null boolean 23 dataSituacao 690 non-null datetime64[ns] 24 textoTotal 690 non-null object 25 investimentoTotal 690 non-null float64 26 investimentoFaixa 687 non-null category 27 isInvestimentoSimbolico 690 non-null bool 28 duracaoPrevista 688 non-null float64 29 lat_viacep 66 non-null float64 30 lon_viacep 66 non-null float64 31 lat_ipedf 92 non-null float64 32 lon_ipedf 92 non-null float64 dtypes: bool(1), boolean(1), category(5), datetime64[ns](6), float64(8), object(12) memory usage: 146.9+ KB
with sqlite3.connect("projeto_investimento.db") as con:
# main
df.to_sql("projeto_investimento", con, if_exists="replace")
# entity tables
instituicoes_df.to_sql("instituicoes", con, if_exists="replace")
eixos_df.to_sql("eixos", con, if_exists="replace")
tipos_df.to_sql("tipos", con, if_exists="replace")
subtipos_df.to_sql("subtipos", con, if_exists="replace")
fontes_de_recurso_df.to_sql("fontes_de_recurso", con, if_exists="replace")
# junction tables
projeto_tomadores_df.to_sql("projeto_tomadores", con, if_exists="replace")
projeto_executores_df.to_sql("projeto_executores", con, if_exists="replace")
projeto_repassadores_df.to_sql("projeto_repassadores", con, if_exists="replace")
projeto_eixos_df.to_sql("projeto_eixos", con, if_exists="replace")
projeto_tipos_df.to_sql("projeto_tipos", con, if_exists="replace")
projeto_subtipos_df.to_sql("projeto_subtipos", con, if_exists="replace")
# with sqlite3.connect("projeto_investimento.db") as con:
# df = pd.read_sql_query("SELECT * FROM projeto_investimento", con)
3. Análise de dados¶
Informações básicas¶
Registros: 712 (após deduplicação de 834 registros originais)
Colunas:
texto
- dados básicos:
idUnico,nome,descricao,funcaoSocial,metaGlobal - descrições opcionais:
naturezaOutras,descPlanoNacionalPoliticaVinculado,descPopulacaoBeneficiada,observacoesPertinentes - criada:
textoTotal
- dados básicos:
geográfico
cep,endereco- criadas:
lat_ipedf,lon_ipedf,lat_viacep,lon_viacep
numéricos
qdtEmpregosGeradospopulacaoBeneficiada- criada:
investimentoTotal
datas
dataInicialPrevista,dataFinalPrevista,dataInicialEfetiva,dataFinalEfetiva,dataCadastro,dataSituacao- criada:
duracaoPrevista
categorias
natureza(Estudo, Obra, Outros, Projeto, Projeto de Investimento em Infraestrutura)situacao(Cadastrada, Cancelada, Concluída, Em execução, Inacabada, Inativada, Paralisada)especie(Ampliação, Construção, Fabricação, Máquinas e Equipamentos, Recuperação, Reforma)isModeladaPorBim(Sim, Não)uf(DF)- criada:
investimentoFaixa(<1, 1-10, 10-100, 100-1k, 1k-10k, 10k-100k, 100k-1M, 1M-10M, 10M-100M, 100M-1B, >1B) - criada:
isInvestimentoSimbolico(Sim, Não)
Colunas com dados aninhados (tomadores, executores, repassadores, eixos, tipos, subTipos, fontesDeRecurso) transformadas em outras tabelas após normalização. Também foram criadas tabelas de junção (projeto_tomadores, projeto_executores, projeto_repassadores, projeto_eixos, projeto_tipos, projeto_subtipos).
Tomadores, executores e repassadores partilham do mesmo sistema de códigos e portanto estão armazenados na mesma tabela.
Eixos, tipos, subtipos poderiam ser armazenados na mesma tabela com perda de normalização mas ganho em simplicidade.
Valores ausentes mais significativos:
- datas efetivas (não previstas): 95% nulos
- empregos gerados e populacao beneficiada: 95% nulos
- dados geográficos (endereco e cep): 50% nulos
df.head()
| idUnico | nome | cep | endereco | descricao | funcaoSocial | metaGlobal | dataInicialPrevista | dataFinalPrevista | dataInicialEfetiva | dataFinalEfetiva | dataCadastro | especie | natureza | naturezaOutras | situacao | descPlanoNacionalPoliticaVinculado | uf | qdtEmpregosGerados | descPopulacaoBeneficiada | populacaoBeneficiada | observacoesPertinentes | isModeladaPorBim | dataSituacao | textoTotal | investimentoTotal | investimentoFaixa | isInvestimentoSimbolico | duracaoPrevista | lat_viacep | lon_viacep | lat_ipedf | lon_ipedf | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1828.53-16 | SISEG - Sistema integrado de segurança | None | None | implantação dos sistemas de monitoramento por ... | implantação dos sistemas de monitoramento por ... | Modernização do sistema de segurança do Banco ... | 2019-02-04 | 2024-02-06 | NaT | NaT | 2021-05-12 | Recuperação | Projeto | Cadastrada | None | DF | NaN | None | NaN | None | <NA> | 2021-05-12 | SISEG - Sistema integrado de segurança\n\nimpl... | 23427554.88 | 10M-100M | False | 1828.0 | NaN | NaN | NaN | NaN | |
| 1 | 1711.53-18 | Reforma do espelho d'água do Edifício-Sede do ... | None | None | Reforma do espelho d'água do Edifício-Sede do ... | Reestabelecer a impermeabilização do espelho d... | Recuperação do sistema de impermeabilização e ... | 2021-01-04 | 2021-05-18 | NaT | NaT | 2021-05-04 | Reforma | Obra | Cadastrada | None | DF | NaN | None | NaN | None | <NA> | 2021-05-04 | Reforma do espelho d'água do Edifício-Sede do ... | 139328.54 | 100k-1M | False | 134.0 | NaN | NaN | NaN | NaN | |
| 2 | 1989.53-01 | Elaboração de projetos arquitetônicos e de eng... | None | Campus Universitário Darcy Ribeiro, Brasília - DF | Contratação de empresa especializada para elab... | Obras para unidades acadêmicas de ensino e pes... | Elaboração de projetos de engenharia e Arquite... | 2021-05-18 | 2021-09-29 | NaT | NaT | 2021-05-17 | Construção | Projeto | None | Cadastrada | None | DF | NaN | None | NaN | None | <NA> | 2021-05-17 | Elaboração de projetos arquitetônicos e de eng... | 1399545.52 | 1M-10M | False | 134.0 | NaN | NaN | NaN | NaN |
| 3 | 2004.53-58 | SISEG - Sistema integrado de segurança | None | None | implantação dos sistemas de monitoramento por ... | implantação dos sistemas de monitoramento por ... | Modernização do sistema de segurança do Banco ... | 2019-02-04 | 2024-02-06 | NaT | NaT | 2021-05-18 | Recuperação | Projeto | Cadastrada | None | DF | NaN | None | NaN | None | <NA> | 2021-05-18 | SISEG - Sistema integrado de segurança\n\nimpl... | 23427554.88 | 10M-100M | False | 1828.0 | NaN | NaN | NaN | NaN | |
| 4 | 2617.53-02 | CENTRO DE FORMAÇÃO TECNOLOGICA - CFT - CAMPUS ... | None | Área Especial n° 01, Quadra 16, Cidade do Auto... | construção um galpão com pé direito alto, que ... | atender a comunidade acadêmica do Campus Estru... | aumentar a oferta de cursos técnicos/tecnológi... | 2020-12-01 | 2021-10-27 | NaT | NaT | 2021-06-18 | Construção | Obra | Cadastrada | triplicar as matrículas da educação profission... | DF | NaN | None | NaN | None | <NA> | 2021-06-18 | CENTRO DE FORMAÇÃO TECNOLOGICA - CFT - CAMPUS ... | 1862560.00 | 1M-10M | False | 330.0 | NaN | NaN | NaN | NaN |
Qualidade¶
Em questão de limpeza do dados, temos vários problemas:
- Valores ausentes: 95% de nulos em campos importantes
- Duplicatas: 87 registros duplicados
- Encoding: 18 projetos com erro de encoding
- Dados de teste: 22 registros falsos (Ronald, Teste)
- Falta de validação: CEPs preenchidos com "1", espaços em branco
- Valores suspeitos: organizações com mais de um código, projetos milionários com duração de zero dias
- Dados simbólicos: Investimentos de R$ 0,01.
Para problemas mais detalhados, ver apendices/qualidade_detalhes.txt
Esses problemas possivelmente indicam:
- Falta de validação e padronização dos dados (e.g. CEP)
- Alteração do formulário com o passar do tempo
- Confusão por parte dos usuários ao preencher os dados
- Campos do formulário que não se adequam bem à realidade dos projetos
Categorias¶
def plot_bim_pie(df: pd.DataFrame, palette: list) -> None:
# Convert boolean to labels with proper ordering
df["bim_label"] = (
df["isModeladaPorBim"].map({True: "Sim", False: "Não"}).fillna("Sem dados")
)
bim_counts = df["bim_label"].value_counts()
fig, ax = plt.subplots(figsize=(4, 4))
# Create combined labels with percentages
combined_labels = [
f"{label}\n({pct:.1f}%)"
for label, count, pct in zip(
bim_counts.index,
bim_counts.values,
bim_counts.values / bim_counts.sum() * 100,
)
]
wedges, texts, autotexts = ax.pie(
bim_counts.values,
labels=combined_labels, # Use combined labels
colors=palette,
autopct="", # Remove separate percentage text
labeldistance=1.25,
wedgeprops=dict(width=0.5, linewidth=0),
)
for text in texts:
text.set_ha("center")
text.set_ha("center")
ax.set_title("Projeto modelado usando BIM\n(Building Information Modeling)")
plt.tight_layout()
plt.show()
plot_bim_pie(df, palette)
def make_barh_plot(
df: pd.DataFrame,
column: str,
title: str,
color: str,
extra_space: float = 1,
show_percentages: bool = True,
labels_inside: bool = False,
):
plt.figure(figsize=(10, 6))
# Get counts and percentages
value_counts = df[column].value_counts(dropna=False)
percentages = (value_counts / len(df)) * 100
# Create DataFrame with both counts and percentages
value_counts_df = pd.DataFrame(
{"count": value_counts, column: value_counts.index, "percentage": percentages}
).reset_index(drop=True)
ax = sns.barplot(
data=value_counts_df,
x="count",
y=column,
hue=column,
orient="h",
order=value_counts_df[column],
palette=[color for _ in range(len(value_counts_df))],
legend=False,
)
# Add labels with count and optionally percentage
for i, v in enumerate(value_counts_df["count"]):
if show_percentages:
label = f"{int(v)} ({percentages.iloc[i]:.1f}%)"
else:
label = f"{int(v)}"
# Position labels inside or outside bars based on labels_inside parameter
if labels_inside:
x_pos = v - 5 # Shift inside
ha = "right"
else:
x_pos = v + 5 # Shift outside
ha = "left"
ax.text(x_pos, i, label, va="center", ha=ha)
# Extend x-axis to accommodate labels if they're outside
max_val = value_counts_df["count"].max()
if not labels_inside:
ax.set_xlim(0, max_val * extra_space)
else:
ax.set_xlim(0, max_val)
plt.title(title)
plt.xlabel("Quantidade")
plt.ylabel("")
plt.tight_layout()
plt.show()
make_barh_plot(df, "natureza", "Natureza dos projetos", palette[0], extra_space=1.55)
make_barh_plot(df, "situacao", "Situação dos projetos", palette[1], extra_space=1.14)
# handle nulls
especie_vis = df.copy()
especie_vis["especie"] = (
especie_vis["especie"].cat.add_categories("Sem dados").fillna("Sem dados")
)
make_barh_plot(
especie_vis, "especie", "Espécies de projetos", palette[2], extra_space=1.15
)
- BIM: Apenas 3.5% dos projetos usou BIM, enquanto 66.2% não usou.
- Natureza dos projetos: A grande maioria dos itens (73%) é classificado como obra, e não como projeto ou projeto de investimento em infraestrutura.
- Situação dos projetos: 76% projetos estão cadastrados, com 11% em execução, 8% concluídos e 3% inativados, cancelados, paralisados ou inacabados.
- Espécies de projetos: 44% construção de novos projetos e 54% reforma, ampliação ou recuperação de projetos existentes.
Investimento¶
Como as variáveis quantitativas qdtEmpregosGerados e populacaoBeneficiada só estão preenchidas em 5% dos casos, evitamos analisá-las.
# Plot normal scale
plt.figure(figsize=(10, 4))
plt.subplot(1, 2, 1)
sns.histplot(
data=df[~df["isInvestimentoSimbolico"]],
x="investimentoTotal",
bins=25,
color=palette[0],
)
plt.xlabel("Investimento (R$)")
plt.ylabel("Projetos")
plt.title("Projetos por Investimento")
# Plot log scale
plt.subplot(1, 2, 2)
sns.histplot(
data=df[~df["isInvestimentoSimbolico"]],
x="investimentoTotal",
bins=25,
color=palette[0],
log_scale=True,
)
plt.xlabel("Investimento (R$)")
plt.ylabel("Projetos")
plt.title("Projetos por Investimento (escala logarítmica)")
plt.tight_layout()
plt.show()
df[~df["isInvestimentoSimbolico"]]["investimentoTotal"].describe().apply(
lambda x: f"{x:,.2f}"
)
count 571.00 mean 13,871,387.02 std 51,931,177.66 min 4,684.45 25% 667,980.23 50% 2,085,189.23 75% 8,402,637.63 max 839,664,954.32 Name: investimentoTotal, dtype: object
def calculate_iqr(data: pd.Series) -> float:
q75 = data.quantile(0.75)
q25 = data.quantile(0.25)
return q75 - q25
valores = df[~df["isInvestimentoSimbolico"]]["investimentoTotal"]
iqr = calculate_iqr(valores)
print(f"IQR: R$ {iqr:,.2f}")
IQR: R$ 7,734,657.40
# Calcular porcentagem de projetos com investimento simbólico vs total
total_projetos = len(df)
projetos_significativos = len(df[~df["isInvestimentoSimbolico"]])
projetos_simbolicos = len(df[df["isInvestimentoSimbolico"]])
print(
f"Projetos com investimento simbólico: {projetos_simbolicos} ({projetos_simbolicos/total_projetos*100:.1f}%)"
)
Projetos com investimento simbólico: 119 (17.2%)
17.2% dos projetos possuem investimento simbólico, isto é, estão registrados com investimentos de menos de um real.
Considerando apenas os investimentos significativos (571 projetos), vemos uma distribuição aproximadamente log-normal (comum com este tipo de dado não-negativo, como dinheiro). Isso significa que grandes investimentos são exponencialmente mais raros que os casos de investimento menor. Assim, isso também indica que os valores mais altos não são outliers, são parte esperada da distribuição log-normal.
Isso também significa que a média (R$ 13,9 milhões) é pouco informativa sobre os dados, pois é muito influenciada pelos valores extremos. Podemos usar mediana e intervalo interquartil (IQR) para ter uma ideia melhor da distribuição.
Os dados apresentam, aproximadamente:
- Valor mínimo de R$ 4,7 mil
- Valor máximo de R$ 840 milhões
- Mediana (valor que divide os dados ao meio): R$ 2,1 milhões
- IQR de R$ 7,7 milhões, ou seja, cerca de 50% dos projetos estão entre R$ 668 mil e R$ 8,4 milhões
Datas¶
Devido à falta de dados para as datas reais, podemos comparar apenas as datas previstas.
date_cols = [
"dataInicialPrevista",
"dataFinalPrevista",
"dataInicialEfetiva",
"dataFinalEfetiva",
"dataCadastro",
"dataSituacao",
]
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(8, 4))
# Plot normal scale
sns.histplot(
data=df["duracaoPrevista"].divide(365),
bins=50,
color=palette[0],
ax=ax1
)
ax1.set_xlabel("Duração Prevista (anos)")
ax1.set_ylabel("Projetos")
ax1.set_title("Projetos por Duração Prevista")
# Plot log scale
sns.histplot(
data=df["duracaoPrevista"].divide(365),
bins=50,
color=palette[0],
log_scale=True,
ax=ax2
)
ax2.set_xlabel("Duração Prevista (anos)")
ax2.set_ylabel("Projetos")
ax2.set_title("Projetos por Duração Prevista\n(escala logarítmica)")
plt.tight_layout()
plt.show()
df["duracaoPrevista"].divide(365).describe().apply(lambda x: f"{x:,.2f}")
count 688.00 mean 2.06 std 1.89 min 0.00 25% 0.80 50% 1.28 75% 2.99 max 13.22 Name: duracaoPrevista, dtype: object
print("IQR:", calculate_iqr(df["duracaoPrevista"].divide(365)))
IQR: 2.187671232876712
A duração prevista também apresenta uma distribuição logarítmica, com os valores:
- Valor mínimo de 0 dias
- Valor máximo de 13 anos
- Mediana: 1,2 anos
- Cerca de 50% dos projetos têm duração entre 0,8 e 3 anos
# valores baixos
df[["descricao", "duracaoPrevista", "investimentoFaixa"]].sort_values(
by="duracaoPrevista", ascending=True
).head()
| descricao | duracaoPrevista | investimentoFaixa | |
|---|---|---|---|
| 304 | CONSTRUÇÃO DE UNIDADE DE ATENÇÃO ESPECIALIZADA... | 0 days | 1M-10M |
| 326 | CONSTRUÇÃO DE UNIDADE DE ATENÇÃO ESPECIALIZADA... | 0 days | 1M-10M |
| 331 | CONSTRUÇÃO DE UNIDADE BÁSICA DE SAÚDE | 0 days | 1M-10M |
| 9 | Obra no Batalhão Escola e Pronto Emprego para ... | 0 days | 10M-100M |
| 287 | CONSTRUÇÃO DE UNIDADE DE ATENÇÃO ESPECIALIZADA... | 1 days | 1M-10M |
Encontramos aqui alguns valores suspeitos, em que projetos com investimentos milionários tem duração prevista de zero dias.
A maior parte dos projetos possui situação de 'cadastrado', com apenas 81 (11%) projetos estarem em situação 'em execução'. Apesar disso, apenas 4 projetos possuem data de início no futuro. A documentação dos dados não entra em detalhes sobre o significado destas categorias ou a atualização dos dados.
def plot_date_distributions(df, palette):
from matplotlib import dates as mdates
# Create subplots for date distributions
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(12, 5), sharey=True)
# Plot initial dates
sns.histplot(data=df, x="dataInicialPrevista", bins=25, color=palette[0], ax=ax1)
ax1.set_title("Datas Iniciais Previstas")
ax1.set_ylabel("Frequência")
ax1.set_xlabel("")
ax1.tick_params(axis="x", rotation=45)
# Plot final dates
sns.histplot(data=df, x="dataFinalPrevista", bins=25, color=palette[0], ax=ax2)
ax2.set_title("Datas Finais Previstas")
ax2.set_ylabel("")
ax2.set_xlabel("")
ax2.tick_params(axis="x", rotation=45)
# Set more frequent x-axis ticks
ax1.xaxis.set_major_locator(mdates.YearLocator(2)) # Show every 2 years
ax2.xaxis.set_major_locator(mdates.YearLocator(2)) # Show every 2 years
# Format dates on x-axis
ax1.xaxis.set_major_formatter(mdates.DateFormatter("%Y"))
ax2.xaxis.set_major_formatter(mdates.DateFormatter("%Y"))
# Adjust layout
plt.tight_layout()
plt.show()
plot_date_distributions(df, palette)
Há dados iniciando em 2004, a projetos com final prevista em 2032. A maiora dos projetos previa iniciar após 2020, com a maior parte terminando antes de 2028. Houve, também, um pico de projetos curtos em 2014.
# Preparar dados
data_for_reg = df[~df["isInvestimentoSimbolico"]].copy()
data_for_reg = data_for_reg[
["idUnico", "descricao", "duracaoPrevista", "investimentoTotal"]
].dropna()
data_for_reg["investimentoTotal_log"] = np.log(data_for_reg["investimentoTotal"])
data_for_reg["duracaoPrevista_log"] = np.log(data_for_reg["duracaoPrevista"])
fig, axes = plt.subplots(1, 2, figsize=(15, 8))
# 1. Escala original
sns.regplot(
data=data_for_reg,
x="duracaoPrevista",
y="investimentoTotal",
scatter_kws={"alpha": 0.5},
line_kws={"color": "red"},
ax=axes[0],
)
axes[0].set_title("Relação entre Duração e Investimento")
axes[0].set_xlabel("Duração Prevista (dias)")
axes[0].set_ylabel("Investimento Total (R$)")
# 2. Log-log
sns.regplot(
data=data_for_reg,
x="duracaoPrevista_log",
y="investimentoTotal_log",
scatter_kws={"alpha": 0.5},
line_kws={"color": "red"},
ax=axes[1],
)
axes[1].set_title("Relação entre Duração e Investimento (log-log)")
axes[1].set_xlabel("Duração Prevista (log dias)")
axes[1].set_ylabel("Investimento Total (log R$)")
plt.tight_layout()
plt.show()
/home/noah-art3mis/projects/takehome-lablivre-analysis/.venv/lib/python3.10/site-packages/pandas/core/arraylike.py:399: RuntimeWarning: divide by zero encountered in log result = getattr(ufunc, method)(*inputs, **kwargs) /home/noah-art3mis/projects/takehome-lablivre-analysis/.venv/lib/python3.10/site-packages/numpy/_core/function_base.py:162: RuntimeWarning: invalid value encountered in multiply y *= step /home/noah-art3mis/projects/takehome-lablivre-analysis/.venv/lib/python3.10/site-packages/numpy/_core/function_base.py:172: RuntimeWarning: invalid value encountered in add y += start /home/noah-art3mis/projects/takehome-lablivre-analysis/.venv/lib/python3.10/site-packages/numpy/lib/_nanfunctions_impl.py:1620: RuntimeWarning: All-NaN slice encountered return fnb._ureduce(a,
pg.corr(
df[~df["isInvestimentoSimbolico"]]["duracaoPrevista"],
df[~df["isInvestimentoSimbolico"]]["investimentoTotal"],
method="spearman", # nao parametrico
)
| n | r | CI95% | p-val | power | |
|---|---|---|---|---|---|
| spearman | 569 | 0.648612 | [0.6, 0.69] | 3.130230e-69 | 1.0 |
X = sm.add_constant(data_for_reg["duracaoPrevista_log"])
y = data_for_reg["investimentoTotal_log"]
model = sm.OLS(y, X).fit()
data_for_reg["residuos"] = model.resid
data_for_reg["residuos_abs"] = np.abs(model.resid)
data_for_reg.nlargest(10, "residuos_abs").assign(
duracaoPrevista_years=lambda x: x["duracaoPrevista"],
investimentoTotal_k=lambda x: x["investimentoTotal"].apply(lambda x: f"{x:,.2f}"),
)[["idUnico", "descricao", "duracaoPrevista", "investimentoTotal_k", "residuos"]].round(
{"duracaoPrevista_years": 1, "investimentoTotal_k": 0, "residuos": 1}
)
| idUnico | descricao | duracaoPrevista | investimentoTotal_k | residuos | |
|---|---|---|---|---|---|
| 287 | 46882.53-02 | CONSTRUÇÃO DE UNIDADE DE ATENÇÃO ESPECIALIZADA... | 1.0 | 2,493,000.00 | 8.1 |
| 374 | 43724.53-06 | Contratação de empresa do ramo de engenharia/a... | 1081.0 | 4,684.45 | -7.1 |
| 117 | 557.53-69 | Projeto Estratégico do SISFRON, cujo objeto é ... | 579.0 | 839,664,954.32 | 5.8 |
| 69 | 10613.53-07 | Prestação de serviços de disponibilização de a... | 1799.0 | 47,700.00 | -5.4 |
| 646 | 5223.53-75 | ELABORAÇÃO DE PROJETOS BÁSICO E EXECUTIVO DE P... | 789.0 | 20,305.41 | -5.2 |
| 516 | 39020.52-43 | execução das obras de adequação de capacidade,... | 629.0 | 352,994,609.29 | 4.9 |
| 549 | 28451.53-40 | ADEQUACAO DE TRECHO RODOVIARIO - ENTRONCAMENTO... | 755.0 | 359,130,057.13 | 4.6 |
| 298 | 1729.53-04 | Prestação de serviços de elaboração de Antepro... | 359.0 | 19,560.00 | -4.2 |
| 420 | 57958.53-86 | Celebrar parcerias referentes ao PRONER | 364.0 | 20,000.00 | -4.2 |
| 389 | 42995.53-70 | Contratação de empresa especializada em arquit... | 352.0 | 21,560.99 | -4.1 |
Considerando apenas os projetos com investimento significativo (n=569), encontramos uma correlação moderada a forte entre a duração prevista e o investimento total (ρ=0.64, IC95%=[0.60, 0.69], p<0.001). Isso é esperado, pois projetos maiores tendem a durar mais.
Com esses dados podemos calcular os projetos mais anômalos: curtos que custam muito ou demorados que custam pouco. Isso é feito usando os resíduos de uma regressão linear, que são mostrados acima. Estes registros mais anômalos são:
| ID | Descrição | Duração | Investimento |
|---|---|---|---|
| 46882.53-02 | CONSTRUÇÃO DE UNIDADE DE ATENÇÃO ESPECIALIZADA... | 1 dia | R$ 2.493.000,00 |
| 43724.53-06 | Contratação de empresa do ramo de engenharia/a... | 3 anos | R$ 4.684,45 |
| 557.53-69 | Projeto Estratégico do SISFRON... | 1.6 anos | R$ 839.664.954,32 |
| 10613.53-07 | Prestação de serviços de disponibilização de a... | 4.9 anos | R$ 47.700,00 |
| 5223.53-75 | ELABORAÇÃO DE PROJETOS BÁSICO E EXECUTIVO DE P... | 2.2 anos | R$ 20.305,41 |
| 39020.52-43 | execução das obras de adequação de capacidade,... | 1.7 anos | R$ 352.994.609,29 |
| 28451.53-40 | ADEQUACAO DE TRECHO RODOVIARIO - ENTRONCAMENTO... | 2.1 anos | R$ 359.130.057,13 |
Dados geográficos (GIS)¶
Mesmo utilizando APIs oficiais, houve dificuldade em obter coordenadas a partir dos CEPs. Ambos os métodos localizaram apenas cerca de metade dos 88 CEPs presentes nos dados.
Apenas 18% de todos os registros possuem representação no mapa — uma amostra que reflete um viés de seleção em duas etapas: primeiro, dos registros que contêm CEP e, depois, dos CEPs que puderam ser convertidos em coordenadas.
Entre os 88 CEPs válidos, as ferramentas de geocodificação do governo encontraram coordenadas para 41 e 33 deles, respectivamente; 29 CEPs foram localizados por ambas — o que não implica necessariamente que as coordenadas coincidem (por exemplo, o CEP 71205-050).
Nos casos com duas coordenadas disponíveis, optou-se pelos dados do IPEDF, uma vez que essa fonte obtém as coordenadas diretamente do CEP, enquanto o ViaCEP realiza a conversão de CEP para endereço, seguida de geocodificação via Nominatim.
def create_investment_map(df):
# Create map centered on DF
m = folium.Map(location=[-15.7942, -47.8822], zoom_start=10)
# Define single color for all markers
COLOR = "#412355" # Purple (LabLivre palette)
# Prepare data with proper coordinate selection
df_map = df[
[
"cep",
"nome",
"descricao",
"lat_ipedf",
"lon_ipedf",
"lat_viacep",
"lon_viacep",
"investimentoTotal",
]
].copy()
# Add markers for each project with coordinates
for idx, row in df_map.iterrows():
lat_ipedf = row["lat_ipedf"]
lon_ipedf = row["lon_ipedf"]
lat_viacep = row["lat_viacep"]
lon_viacep = row["lon_viacep"]
# Determine which coordinates to use (prioritize IPEDF)
if pd.notna(lat_ipedf) and pd.notna(lon_ipedf):
lat, lon = lat_ipedf, lon_ipedf
source = "IPEDF"
elif pd.notna(lat_viacep) and pd.notna(lon_viacep):
lat, lon = lat_viacep, lon_viacep
source = "ViaCEP"
else:
# No coordinates available
continue
# Prepare popup text
popup_text = (
f"""
<b>CEP:</b> {row['cep']}<br>
<b>Fonte:</b> {source}<br>
<b>Nome:</b> {row['nome'][:50]}...<br>
<b>Investimento:</b> R$ {row['investimentoTotal']:,.2f}
"""
if pd.notna(row["investimentoTotal"])
else f"""
<b>CEP:</b> {row['cep']}<br>
<b>Fonte:</b> {source}<br>
<b>Nome:</b> {row['nome'][:50]}...
"""
)
# Add marker with radius scaled by investment amount
folium.CircleMarker(
location=[lat, lon],
radius=(
np.sqrt(row["investimentoTotal"] / 100000)
if pd.notna(row["investimentoTotal"])
else 5
),
color=COLOR,
fill=True,
fillColor=COLOR,
fillOpacity=0.7,
popup=folium.Popup(popup_text, max_width=300),
tooltip=f"{row['cep']} - {source}",
).add_to(m)
return m
m = create_investment_map(df)
m
O mapa é interativo e revela uma concentração de investimentos na região da Asa Sul. Também há presença significativa em Ceilândia, Gama e Planaltina -- áreas que, em geral, possuem maior densidade populacional. Fora dessas regiões, observam-se poucos ou nenhum investimento registrado (como em São Sebastião, por exemplo).
Parte da ausência de pontos no mapa pode estar relacionada tanto ao processo de seleção dos dados quanto à disponibilidade dos CEPs nas APIs de geocodificação utilizadas.
Processamento de linguagem natural (NLP)¶
Podemos usar as colunas que contem texto para analisar padrões semânticos nos dados. Aqui, usamos embeddings com redução de dimensionalidade e agrupamento (clustering).
O modelo de embedding se beneficia de ser usado em um computador com GPU. A produção dos embeddings foi feita na nuvem e salvos em um arquivo que é aberto aqui.
# df[['idUnico', 'textoTotal']].to_parquet('data/projetos_sem_vetores.parquet')
# # fazer isso aqui usando uma GPU
# def get_embeddings(df: pd.DataFrame, model: SentenceTransformer) -> pd.DataFrame:
# # https://huggingface.co/google/embeddinggemma-300m
# # requires HF_TOKEN env variable
# from dotenv import load_dotenv
# load_dotenv()
# # gated model: only unlocks if you sign the terms and conditions consent form in the huggingface model page
# from sentence_transformers import SentenceTransformer
# model = SentenceTransformer("google/embeddinggemma-300m")
# sentences = df["textoTotal"].tolist()
# embeddings = model.encode(
# sentences=sentences,
# prompt_name="Clustering", # gemma specific
# show_progress_bar=True,
# convert_to_numpy=True,
# )
# sentences_with_embeddings = pd.DataFrame(
# {"texto": sentences, "embeddings": embeddings.tolist()}
# )
# df_with_embeddings = pd.merge(
# df, sentences_with_embeddings, left_on="textoTotal", right_on="texto"
# )
# df_with_embeddings = df_with_embeddings.drop("texto", axis=1)
# return df_with_embeddings
# textos_df = pd.read_parquet('data/projetos_sem_vetores.parquet')
# df_with_embeddings = get_embeddings(textos_df, model)
# df_with_embeddings.to_parquet('data/projetos_com_vetores.parquet')
embeddings = pd.read_parquet("data/projetos_com_vetores.parquet")
df = df.merge(embeddings.drop("textoTotal", axis=1), on="idUnico", how="left")
def umap_df(
df: pd.DataFrame, vectors_col: str, dimension_names: list[str]
) -> pd.DataFrame:
# Based on https://umap-learn.readthedocs.io/en/latest/
reducer = umap.UMAP(
n_components=2,
n_neighbors=15, # Default from documentation
min_dist=0.1, # Default from documentation
metric="euclidean", # Default metric
random_state=42,
verbose=False,
)
umap_components = reducer.fit_transform(df[vectors_col].tolist())
umap_df = pd.DataFrame(umap_components, columns=dimension_names) # type: ignore
return pd.concat([df, umap_df], axis=1)
def hdbscan_df(
df: pd.DataFrame, dimension_names: list[str], min_cluster_size: int
) -> pd.DataFrame:
# https://github.com/scikit-learn-contrib/hdbscan
clusterer = hdbscan.HDBSCAN(min_cluster_size)
clusters = clusterer.fit_predict(df[dimension_names])
df["cluster"] = clusters
df["cluster"] = df["cluster"].astype(str)
return df
dimension_names = ["UMAP_1", "UMAP_2"]
df = umap_df(df, "embeddings", dimension_names)
df = hdbscan_df(df, dimension_names, min_cluster_size=20)
/home/noah-art3mis/projects/takehome-lablivre-analysis/.venv/lib/python3.10/site-packages/umap/umap_.py:1952: UserWarning: n_jobs value 1 overridden to 1 by setting random_state. Use no seed for parallelism. warn(
/home/noah-art3mis/projects/takehome-lablivre-analysis/.venv/lib/python3.10/site-packages/sklearn/utils/deprecation.py:132: FutureWarning: 'force_all_finite' was renamed to 'ensure_all_finite' in 1.6 and will be removed in 1.8. warnings.warn( /home/noah-art3mis/projects/takehome-lablivre-analysis/.venv/lib/python3.10/site-packages/sklearn/utils/deprecation.py:132: FutureWarning: 'force_all_finite' was renamed to 'ensure_all_finite' in 1.6 and will be removed in 1.8. warnings.warn(
def plot_embeddings_interactive(
df: pd.DataFrame, dimension_names: list[str], hue: str
) -> alt.Chart:
chart = (
alt.Chart(df)
.mark_circle(size=100, opacity=0.7, strokeWidth=0.5, stroke="white")
.encode(
x=alt.X(dimension_names[0], title="UMAP1"),
y=alt.Y(dimension_names[1], title="UMAP2"),
color=alt.Color(
hue,
scale=alt.Scale(scheme="tableau10"),
legend=alt.Legend(title="Cluster"),
),
tooltip=[
alt.Tooltip(hue, title="Grupo"),
alt.Tooltip("idUnico", title="ID"),
alt.Tooltip("descricao", title="Descrição"),
# alt.Tooltip("investimentoTotal", title="Investimento"),
alt.Tooltip("duracaoPrevista", title="Duração Prevista"),
alt.Tooltip("natureza", title="Natureza"),
# alt.Tooltip("situacao", title="Situação"),
alt.Tooltip("especie", title="Espécie"),
alt.Tooltip("investimentoFaixa", title="Investimento Faixa"),
# alt.Tooltip("dataInicialPrevista", title="Data Início Prevista"),
# alt.Tooltip("dataFinalPrevista", title="Data Final Prevista"),
],
)
.properties(
width=600,
height=400,
title="Representação UMAP dos embeddings dos projetos com agrupamento (HDBSCAN)",
)
.interactive()
) # Enables pan and zoom
return chart
# altair does not recognise timedelta64[ns]
df_nlp_vis = df.copy()
df_nlp_vis["duracaoPrevista"] = df_nlp_vis["duracaoPrevista"].dt.days
plot_embeddings_interactive(df_nlp_vis, dimension_names, hue="cluster")